package com.fzk.blog.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fzk.blog.bean.Tag;
import com.fzk.blog.bean.vo.TagInfo;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @author fzk
 * @date 2021-08-15 16:05
 */
public interface TagMapper extends BaseMapper<Tag> {
    @Select("SELECT t.id,t.name,t.description,COUNT(b.id) AS blogNumber " +
            "   FROM tbl_tag t " +
            "   LEFT JOIN tbl_blog_tag_reference r ON (t.id=r.tag_id AND r.flag=0) " +
            "   LEFT JOIN tbl_blog b ON (r.blog_id=b.id AND b.flag=0 AND b.published=TRUE) " +
            "   WHERE t.flag=0 " +
            "   GROUP BY t.id" +
            "   order by blogNumber DESC")
    List<TagInfo> getTagInfoList();

    @Select("SELECT t.id,t.name,t.description,COUNT(b.id) AS blogNumber " +
            "   FROM tbl_tag t " +
            "   LEFT JOIN tbl_blog_tag_reference r ON (t.id=r.tag_id AND r.flag=0) " +
            "   LEFT JOIN tbl_blog b ON (r.blog_id=b.id AND b.flag=0 AND b.published=TRUE) " +
            "   WHERE t.flag=0 AND t.id=#{id} " +
            "   GROUP BY t.id ")
    TagInfo getTagInfoById(int id);

    @Select("SELECT * FROM tbl_tag WHERE name=#{name} AND flag=0")
    List<Tag> selectByName(String name);

    @Update("UPDATE tbl_tag SET edit_by=#{loginId},flag=1 WHERE id=#{tagId} AND flag=0 ")
    int logicalDeleteById(int tagId, int loginId);

    @Select("SELECT t.id,t.name,t.description " +
            "   FROM tbl_tag t INNER JOIN tbl_blog_tag_reference r ON (t.id=r.tag_id AND r.flag=0) " +
            "   INNER JOIN tbl_blog b ON r.blog_id=b.id " +
            "   WHERE b.id=#{blogId} AND t.flag=0")
    List<Tag> getTagListByBlogId(int blogId);
}
